"""
Case Type   : DBlink功能
Case Name   : dblink查询结果为空
Create At   : 2024/7/1
Owner       : peilinqian
Description :
    step1: 创建数据库及库下创建dblink扩展;
    step2: 目标库进行表创建;
    step3: 进行dblink连接查询表结果为0行;
Expect      :
    step1: 创建数据库及库下创建dblink扩展; expect:成功
    step2: 目标库进行表创建; expect:成功
    step3: 进行dblink连接查询表结果为0行; expect:成功
History     :
"""
import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node


class DblinkCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)}:初始化----')
        self.pri_dbuser = Node(node='PrimaryDbUser')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.com = Common()
        self.constant = Constant()
        self.db_name_1 = 'db_dblink_0025_1'
        self.db_name_2 = 'db_dblink_0025_2'
        self.tb_name = 't_dblink_0025'
        self.err_flag = 'ERROR:  dblink not support in thread pool'
        self.log.info('----查询参数----')
        self.init_para = self.com.show_param("enable_thread_pool")

    def test_main(self):
        step_txt = '----step1: 创建数据库及库下创建dblink扩展; expect:成功----'
        self.log.info(step_txt)
        db_sql = f"drop database if exists {self.db_name_1};" \
                 f"drop database if exists {self.db_name_2};" \
                 f"create database {self.db_name_1};" \
                 f"create database {self.db_name_2};"
        db_result = self.pri_sh.execut_db_sql(db_sql)
        self.log.info(db_result)
        create_sql = f"create extension dblink;"
        create_result = self.pri_sh.execut_db_sql(create_sql,
                                                  dbname=self.db_name_1)
        self.log.info(create_result)
        self.assertEqual(db_result.count(self.constant.CREATE_DATABASE_SUCCESS),
                         2,
                         "执行失败" + step_txt)
        self.assertIn(self.constant.create_extension_success, create_result,
                      "执行失败" + step_txt)

        step_txt = '----step2: 目标库进行表创建; expect:成功----'
        self.log.info(step_txt)
        sql = f"create table {self.tb_name} (id int,name text)"
        result = self.pri_sh.execut_db_sql(sql,
                                           dbname=self.db_name_2)
        self.log.info(result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, result,
                      "执行失败" + step_txt)

        step_txt = '----step3: 进行dblink连接查询表结果为0行; expect:成功----'
        self.log.info(step_txt)
        sql = f"select * from dblink('dbname={self.db_name_2}'," \
              f"'select * from {self.tb_name}') as t(id int, name text)"
        result = self.pri_sh.execut_db_sql(sql,
                                           dbname=self.db_name_1)
        self.log.info(result)
        self.assertIn('0 rows', result, "执行失败" + step_txt)

    def tearDown(self):
        self.log.info('----this is tearDown----')
        step6_txt = '----dblink侧清理数据; expect:清理成功----'
        self.log.info(step6_txt)
        drop_sql = f"drop database {self.db_name_1};"
        msg1 = self.pri_sh.execut_db_sql(drop_sql)
        self.log.info(msg1)

        step8_txt = '----目标库进行数据库及用户删除; expect:成功----'
        self.log.info(step8_txt)
        drop_db_sql = f"clean connection to all force for database " \
                      f"{self.db_name_2};" \
                      f"drop database if exists {self.db_name_2};"
        drop_db_msg = self.pri_sh.execut_db_sql(drop_db_sql)
        self.log.info(drop_db_msg)

        self.log.info('----teardown断言----')
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, msg1,
                      "执行失败" + step6_txt)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, drop_db_msg,
                      "执行失败" + step8_txt)

        self.log.info(f'----{os.path.basename(__file__)}:执行完毕----')
